package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.NewsDao;
import com.windfone.model.News;
import com.windfone.model.NewsType;

public class NewsDaoImpl implements NewsDao {
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}

	String DELETE_SQL = " DELETE FROM  news WHERE id=? ";
	public int deleteNews(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_ALL_NUMBER_SQL = " SELECT COUNT(1) FROM  news  ";
	public int getAllRecorderNumber() {
		final List<Integer> result = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
					GET_ALL_NUMBER_SQL,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}

	String UPDATE_SQL = " UPDATE news SET newsTypeId=? ,userId=? , content=?,  title=?,summary=? WHERE id=? ";
	public int updateNews(final News bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getNewsTypeId());
						ps.setInt(i++,bean.getUserId());
//						ps.setDate(i++,new java.sql.Date(bean.getCreateTime().getTime()));
						ps.setString(i++,bean.getContent());
						ps.setString(i++,bean.getTitle());
						ps.setString(i++,bean.getSummary());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	
	String INSERT_SQL = " INSERT INTO news (newsTypeId,userId, content,title,summary) " +
	"VALUES( ?,?,?  ,?,? ) ";
	public int insertNews(final News bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getNewsTypeId());
						ps.setInt(i++,bean.getUserId());
//						ps.setDate(i++,new java.sql.Date(bean.getCreateTime().getTime()));
						ps.setString(i++,bean.getContent());
						ps.setString(i++,bean.getTitle());
						ps.setString(i++,bean.getSummary());
//						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_BYID_SQL = " SELECT * FROM  news  WHERE id=? ";
	public List<News> getNewsById(final int keyValue) {
		final List<News> result = new ArrayList<News>();
		try {
			jdbcTemplate.query(
					GET_BYID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							News to= new News();
 							to.setId(rs.getInt("id"));
 							to.setContent(rs.getString("content"));
 							to.setTitle(rs.getString("title"));
 							to.setSummary(rs.getString("summary"));
 							to.setUserId(rs.getInt("userId"));
 							to.setNewsTypeId(rs.getInt("newsTypeId"));
 							to.setCreateTime(rs.getTimestamp("createTime"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	String GET_ALL_SQL = "  SELECT * FROM  news  ORDER BY id ";
	public List<News> getAllNews(final int beginRecord) {
		final List<News> result = new ArrayList<News>();
		String sql = GET_ALL_SQL + " LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						News to= new News();
						to.setId(rs.getInt("id"));
						to.setContent(rs.getString("content"));
						to.setTitle(rs.getString("title"));
						to.setSummary(rs.getString("summary"));
						to.setUserId(rs.getInt("userId"));
						to.setNewsTypeId(rs.getInt("newsTypeId"));
						to.setCreateTime(rs.getTimestamp("createTime"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	String DELETE_TYPE_SQL = " DELETE FROM  news WHERE id=? ";
	public int deleteNewsType(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_TYPE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
 
	
	String UPDATE_TYPE_SQL = " UPDATE newstype SET typeName=?,description=? WHERE id=? ";
	public int updateNewsType(final NewsType bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_TYPE_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setString(i++,bean.getTypeName());
							ps.setString(i++,bean.getDescription());
							ps.setInt(i++,bean.getId());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}


	String INSERT_TYPE_SQL = " INSERT INTO newstype (typeName,description) VALUES( ?,?) ";
	public int insertNewsType(final NewsType bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				INSERT_TYPE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getTypeName());
						ps.setString(i++,bean.getDescription());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
 
	String GET_ALL_TYPE_SQL = "  SELECT * FROM  newstype    ";
	public List<NewsType> getAllNewsType() {
		final List<NewsType> result = new ArrayList<NewsType>();
		String sql = GET_ALL_TYPE_SQL ;
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						NewsType to= new NewsType();
						to.setId(rs.getInt("id"));
						to.setTypeName(rs.getString("typeName"));
						to.setDescription(rs.getString("description"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	String GET_NEWTYPE_SQL = " SELECT * FROM newstype where id=? ";
	public List<NewsType> getNewsTypeById(final int keyValue) {
		final List<NewsType> result = new ArrayList<NewsType>();
		try {
			jdbcTemplate.query(
					GET_NEWTYPE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							NewsType to= new NewsType();
 							to.setId(rs.getInt("id"));
 							to.setTypeName(rs.getString("typeName"));
 							to.setDescription(rs.getString("description"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
 
	String GET_ALL_NEWS_TYPE_NUMBER_SQL = " SELECT COUNT(1) FROM  newstype  ";
	public int getAllNewsTypeRecorderNumber() {
		final List<Integer> result = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
					GET_ALL_NEWS_TYPE_NUMBER_SQL,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	
	String GET_NEWSBY_TYPEID_SQL = " SELECT * FROM  news  WHERE newsTypeId=? ";
	public List<News> getNewsByNewtypeId(final int keyValue) {
		final List<News> result = new ArrayList<News>();
		try {
			jdbcTemplate.query(
					GET_NEWSBY_TYPEID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							News to= new News();
 							to.setId(rs.getInt("id"));
 							to.setContent(rs.getString("content"));
 							to.setTitle(rs.getString("title"));
 							to.setSummary(rs.getString("summary"));
 							to.setUserId(rs.getInt("userId"));
 							to.setNewsTypeId(rs.getInt("newsTypeId"));
 							to.setCreateTime(rs.getTimestamp("createTime"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}
